using System;
using System.Data;
using System.Configuration;
using CodeWork.Library.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Data.SqlTypes;
using CodeWork.Library;
using System.Text;
using System.Data.SqlClient;

/// <summary>
/// ORM object that maps to tbl_SiteUser
/// </summary>
namespace EasyCommerce.DAL
{
    public class SiteUser : SqlServerDAO
    {
        /// <summary>
        /// Self table parameters
        /// </summary>
        private int _id;
		private string _userName;
		private string _userPassword;
		private string _emailAddress;
		private bool _isActive;
		private bool _isOnline;
		private bool _isConfirmed;
		private string _confirmationCode;
		private DateTime _registerDate;
		private DateTime _lastLogin;
		private string _securityQuestion;
		private string _securityAnswer;
		private int _visitCount;
		private string _registerIp;
		private int _failedLoginCount;
		private int _userRoleId;

        /// <summary>
        /// Foriegn key parameters
        /// </summary>
        private List<GiftCoupon> _purchasedGiftCoupon;
		private bool _isPurchasedGiftCouponLoaded;
		private List<GiftCoupon> _recievedGiftCoupon;
		private bool _isRecievedGiftCouponLoaded;
		private List<ReviewComment> _reviewerReviewComment;
		private bool _isReviewerReviewCommentLoaded;
		private List<SaleOrder> _orderedSaleOrder;
		private bool _isOrderedSaleOrderLoaded;
		private List<UserProfile> _profileUserProfile;
		private bool _isProfileUserProfileLoaded;
		

        /// <summary>
        /// Self table properties
        /// </summary>
		public int Id
		{
			get { return this._id; }
			set { this._id = value; }
		}

		public string UserName
		{
			get { return this._userName; }
			set { this._userName = value; }
		}

		public string UserPassword
		{
			get { return this._userPassword; }
			set { this._userPassword = value; }
		}

		public string EmailAddress
		{
			get { return this._emailAddress; }
			set { this._emailAddress = value; }
		}

		public bool IsActive
		{
			get { return this._isActive; }
			set { this._isActive = value; }
		}

		public bool IsOnline
		{
			get { return this._isOnline; }
			set { this._isOnline = value; }
		}

		public bool IsConfirmed
		{
			get { return this._isConfirmed; }
			set { this._isConfirmed = value; }
		}

		public string ConfirmationCode
		{
			get { return this._confirmationCode; }
			set { this._confirmationCode = value; }
		}

		public DateTime RegisterDate
		{
			get { return this._registerDate; }
			set { this._registerDate = value; }
		}

		public DateTime LastLogin
		{
			get { return this._lastLogin; }
			set { this._lastLogin = value; }
		}

		public string SecurityQuestion
		{
			get { return this._securityQuestion; }
			set { this._securityQuestion = value; }
		}

		public string SecurityAnswer
		{
			get { return this._securityAnswer; }
			set { this._securityAnswer = value; }
		}

		public int VisitCount
		{
			get { return this._visitCount; }
			set { this._visitCount = value; }
		}

		public string RegisterIp
		{
			get { return this._registerIp; }
			set { this._registerIp = value; }
		}

		public int FailedLoginCount
		{
			get { return this._failedLoginCount; }
			set { this._failedLoginCount = value; }
		}

		public int UserRoleId
		{
			get { return this._userRoleId; }
			set { this._userRoleId = value; }
		}
		
        /// <summary>
        /// Foreign key properties, loaded on demand only
        /// </summary>
        public List<GiftCoupon> PurchasedGiftCoupon
		{
			get
			{
				if (!this._isPurchasedGiftCouponLoaded)
				{
					List<Condition> conditions = new List<Condition>();
					conditions.Add(new Condition("purchased_by_siteuser_id", ConditionOperator.Equal,this._id, JoinOperator.FirstValue));
					GiftCoupon giftCoupon = new GiftCoupon();
					this._purchasedGiftCoupon = giftCoupon.Load(conditions);
					this._isPurchasedGiftCouponLoaded = true;
				}
				return this._purchasedGiftCoupon;
			}
			set
			{
				this._purchasedGiftCoupon = value;
				this._isPurchasedGiftCouponLoaded = true;
			}
		}
		public List<GiftCoupon> RecievedGiftCoupon
		{
			get
			{
				if (!this._isRecievedGiftCouponLoaded)
				{
					List<Condition> conditions = new List<Condition>();
					conditions.Add(new Condition("recieved_by_siteuser_id", ConditionOperator.Equal,this._id, JoinOperator.FirstValue));
					GiftCoupon giftCoupon = new GiftCoupon();
					this._recievedGiftCoupon = giftCoupon.Load(conditions);
					this._isRecievedGiftCouponLoaded = true;
				}
				return this._recievedGiftCoupon;
			}
			set
			{
				this._recievedGiftCoupon = value;
				this._isRecievedGiftCouponLoaded = true;
			}
		}
		public List<ReviewComment> ReviewerReviewComment
		{
			get
			{
				if (!this._isReviewerReviewCommentLoaded)
				{
					List<Condition> conditions = new List<Condition>();
					conditions.Add(new Condition("reviewer_siteuser_id", ConditionOperator.Equal,this._id, JoinOperator.FirstValue));
					ReviewComment reviewComment = new ReviewComment();
					this._reviewerReviewComment = reviewComment.Load(conditions);
					this._isReviewerReviewCommentLoaded = true;
				}
				return this._reviewerReviewComment;
			}
			set
			{
				this._reviewerReviewComment = value;
				this._isReviewerReviewCommentLoaded = true;
			}
		}
		public List<SaleOrder> OrderedSaleOrder
		{
			get
			{
				if (!this._isOrderedSaleOrderLoaded)
				{
					List<Condition> conditions = new List<Condition>();
					conditions.Add(new Condition("ordered_by_siteuser_id", ConditionOperator.Equal,this._id, JoinOperator.FirstValue));
					SaleOrder saleOrder = new SaleOrder();
					this._orderedSaleOrder = saleOrder.Load(conditions);
					this._isOrderedSaleOrderLoaded = true;
				}
				return this._orderedSaleOrder;
			}
			set
			{
				this._orderedSaleOrder = value;
				this._isOrderedSaleOrderLoaded = true;
			}
		}
		public List<UserProfile> ProfileUserProfile
		{
			get
			{
				if (!this._isProfileUserProfileLoaded)
				{
					List<Condition> conditions = new List<Condition>();
					conditions.Add(new Condition("profile_for_siteuser_id", ConditionOperator.Equal,this._id, JoinOperator.FirstValue));
					UserProfile userProfile = new UserProfile();
					this._profileUserProfile = userProfile.Load(conditions);
					this._isProfileUserProfileLoaded = true;
				}
				return this._profileUserProfile;
			}
			set
			{
				this._profileUserProfile = value;
				this._isProfileUserProfileLoaded = true;
			}
		}
		

        /// <summary>
        /// Empty Constructor
        /// </summary>
        public SiteUser() { }

        /// <summary>
        /// Parameterized constructor
        /// </summary>
        /// <param name="id"></param>
		/// <param name="userName"></param>
		/// <param name="userPassword"></param>
		/// <param name="emailAddress"></param>
		/// <param name="isActive"></param>
		/// <param name="isOnline"></param>
		/// <param name="isConfirmed"></param>
		/// <param name="confirmationCode"></param>
		/// <param name="registerDate"></param>
		/// <param name="lastLogin"></param>
		/// <param name="securityQuestion"></param>
		/// <param name="securityAnswer"></param>
		/// <param name="visitCount"></param>
		/// <param name="registerIp"></param>
		/// <param name="failedLoginCount"></param>
		/// <param name="userRoleId"></param>
        public SiteUser
        (
            int id,
			string userName,
			string userPassword,
			string emailAddress,
			bool isActive,
			bool isOnline,
			bool isConfirmed,
			string confirmationCode,
			DateTime registerDate,
			DateTime lastLogin,
			string securityQuestion,
			string securityAnswer,
			int visitCount,
			string registerIp,
			int failedLoginCount,
			int userRoleId
        )
        {
            this._id = id;
			this._userName = userName;
			this._userPassword = userPassword;
			this._emailAddress = emailAddress;
			this._isActive = isActive;
			this._isOnline = isOnline;
			this._isConfirmed = isConfirmed;
			this._confirmationCode = confirmationCode;
			this._registerDate = registerDate;
			this._lastLogin = lastLogin;
			this._securityQuestion = securityQuestion;
			this._securityAnswer = securityAnswer;
			this._visitCount = visitCount;
			this._registerIp = registerIp;
			this._failedLoginCount = failedLoginCount;
			this._userRoleId = userRoleId;
        }

        /// <summary>
        /// Static save method. Saves the current instance in the table using the provided values using the
        /// primary key id. Expects that the Id is a primary key of an existing record in table.
        /// </summary>
        /// <param name="id"></param>
		/// <param name="userName"></param>
		/// <param name="userPassword"></param>
		/// <param name="emailAddress"></param>
		/// <param name="isActive"></param>
		/// <param name="isOnline"></param>
		/// <param name="isConfirmed"></param>
		/// <param name="confirmationCode"></param>
		/// <param name="registerDate"></param>
		/// <param name="lastLogin"></param>
		/// <param name="securityQuestion"></param>
		/// <param name="securityAnswer"></param>
		/// <param name="visitCount"></param>
		/// <param name="registerIp"></param>
		/// <param name="failedLoginCount"></param>
		/// <param name="userRoleId"></param>
        /// <returns>Boolean result as success or failure</returns>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
        public static bool Save
        (
            int id,
			string userName,
			string userPassword,
			string emailAddress,
			bool isActive,
			bool isOnline,
			bool isConfirmed,
			string confirmationCode,
			DateTime registerDate,
			DateTime lastLogin,
			string securityQuestion,
			string securityAnswer,
			int visitCount,
			string registerIp,
			int failedLoginCount,
			int userRoleId
        )
        {
            SiteUser siteUser = new SiteUser();
            siteUser.Id = id;
			siteUser.UserName = userName;
			siteUser.UserPassword = userPassword;
			siteUser.EmailAddress = emailAddress;
			siteUser.IsActive = isActive;
			siteUser.IsOnline = isOnline;
			siteUser.IsConfirmed = isConfirmed;
			siteUser.ConfirmationCode = confirmationCode;
			siteUser.RegisterDate = registerDate;
			siteUser.LastLogin = lastLogin;
			siteUser.SecurityQuestion = securityQuestion;
			siteUser.SecurityAnswer = securityAnswer;
			siteUser.VisitCount = visitCount;
			siteUser.RegisterIp = registerIp;
			siteUser.FailedLoginCount = failedLoginCount;
			siteUser.UserRoleId = userRoleId;

            return siteUser.Save();
        }

        /// <summary>
        /// Saves the current instance and returns result
        /// </summary>
        /// <returns>Boolean result as success or failure</returns>
        public override bool Save()
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddCommandParameter(command, "@Id", SqlDbType.Int, 0, ParameterDirection.Input, Id);
			AddCommandParameter(command, "@UserName", SqlDbType.NVarChar, 50, ParameterDirection.Input, UserName);
			AddCommandParameter(command, "@UserPassword", SqlDbType.NVarChar, 50, ParameterDirection.Input, UserPassword);
			AddCommandParameter(command, "@EmailAddress", SqlDbType.NVarChar, 50, ParameterDirection.Input, EmailAddress);
			AddCommandParameter(command, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, IsActive);
			AddCommandParameter(command, "@IsOnline", SqlDbType.Bit, 0, ParameterDirection.Input, IsOnline);
			AddCommandParameter(command, "@IsConfirmed", SqlDbType.Bit, 0, ParameterDirection.Input, IsConfirmed);
			AddCommandParameter(command, "@ConfirmationCode", SqlDbType.NVarChar, 300, ParameterDirection.Input, ConfirmationCode);
			AddCommandParameter(command, "@RegisterDate", SqlDbType.DateTime, 0, ParameterDirection.Input, RegisterDate);
			AddCommandParameter(command, "@LastLogin", SqlDbType.DateTime, 0, ParameterDirection.Input, LastLogin);
			AddCommandParameter(command, "@SecurityQuestion", SqlDbType.NVarChar, 300, ParameterDirection.Input, SecurityQuestion);
			AddCommandParameter(command, "@SecurityAnswer", SqlDbType.NVarChar, 300, ParameterDirection.Input, SecurityAnswer);
			AddCommandParameter(command, "@VisitCount", SqlDbType.Int, 0, ParameterDirection.Input, VisitCount);
			AddCommandParameter(command, "@RegisterIp", SqlDbType.NVarChar, 50, ParameterDirection.Input, RegisterIp);
			AddCommandParameter(command, "@FailedLoginCount", SqlDbType.Int, 0, ParameterDirection.Input, FailedLoginCount);
			AddCommandParameter(command, "@UserRoleId", SqlDbType.Int, 0, ParameterDirection.Input, UserRoleId);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_SiteUser_Save.ToString());

            int returnValue = (int)GetOneData(command);
            return (returnValue == 0 ? false : true);
        }

        /// <summary>
        /// Saves the current instance based on provided conditions. This method assumes that
        /// conditions are provided in correct order and in proper format.
        /// </summary>
        /// <param name="conditions">Custom condition based on which to match data</param>
        /// <returns>Boolean result as success or failure</returns>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
        public bool Save(List<Condition> conditions)
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            PrepareConditionalParameters(command, conditions);

            AddCommandParameter(command, "@NewUserName", SqlDbType.NVarChar, 50, ParameterDirection.Input, UserName);
			AddCommandParameter(command, "@NewUserPassword", SqlDbType.NVarChar, 50, ParameterDirection.Input, UserPassword);
			AddCommandParameter(command, "@NewEmailAddress", SqlDbType.NVarChar, 50, ParameterDirection.Input, EmailAddress);
			AddCommandParameter(command, "@NewIsActive", SqlDbType.Bit, 0, ParameterDirection.Input, IsActive);
			AddCommandParameter(command, "@NewIsOnline", SqlDbType.Bit, 0, ParameterDirection.Input, IsOnline);
			AddCommandParameter(command, "@NewIsConfirmed", SqlDbType.Bit, 0, ParameterDirection.Input, IsConfirmed);
			AddCommandParameter(command, "@NewConfirmationCode", SqlDbType.NVarChar, 300, ParameterDirection.Input, ConfirmationCode);
			AddCommandParameter(command, "@NewRegisterDate", SqlDbType.DateTime, 0, ParameterDirection.Input, RegisterDate);
			AddCommandParameter(command, "@NewLastLogin", SqlDbType.DateTime, 0, ParameterDirection.Input, LastLogin);
			AddCommandParameter(command, "@NewSecurityQuestion", SqlDbType.NVarChar, 300, ParameterDirection.Input, SecurityQuestion);
			AddCommandParameter(command, "@NewSecurityAnswer", SqlDbType.NVarChar, 300, ParameterDirection.Input, SecurityAnswer);
			AddCommandParameter(command, "@NewVisitCount", SqlDbType.Int, 0, ParameterDirection.Input, VisitCount);
			AddCommandParameter(command, "@NewRegisterIp", SqlDbType.NVarChar, 50, ParameterDirection.Input, RegisterIp);
			AddCommandParameter(command, "@NewFailedLoginCount", SqlDbType.Int, 0, ParameterDirection.Input, FailedLoginCount);
			AddCommandParameter(command, "@NewUserRoleId", SqlDbType.Int, 0, ParameterDirection.Input, UserRoleId);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_SiteUser_SaveConditional.ToString());

            int returnValue = (int)GetOneData(command);
            return (returnValue == 0 ? false : true);
        }

        /// <summary>
        /// Static creational method. This method actually adds a new entry in the table with
        /// the provided information.
        /// </summary>
        /// <param name="userName"></param>
		/// <param name="userPassword"></param>
		/// <param name="emailAddress"></param>
		/// <param name="isActive"></param>
		/// <param name="isOnline"></param>
		/// <param name="isConfirmed"></param>
		/// <param name="confirmationCode"></param>
		/// <param name="registerDate"></param>
		/// <param name="lastLogin"></param>
		/// <param name="securityQuestion"></param>
		/// <param name="securityAnswer"></param>
		/// <param name="visitCount"></param>
		/// <param name="registerIp"></param>
		/// <param name="failedLoginCount"></param>
		/// <param name="userRoleId"></param>
        /// <returns>Instance of the created object</returns>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
        public static SiteUser Create
        (
            string userName,
			string userPassword,
			string emailAddress,
			bool isActive,
			bool isOnline,
			bool isConfirmed,
			string confirmationCode,
			DateTime registerDate,
			DateTime lastLogin,
			string securityQuestion,
			string securityAnswer,
			int visitCount,
			string registerIp,
			int failedLoginCount,
			int userRoleId
        )
        {
            SiteUser siteUser = new SiteUser();
            siteUser.UserName = userName;
			siteUser.UserPassword = userPassword;
			siteUser.EmailAddress = emailAddress;
			siteUser.IsActive = isActive;
			siteUser.IsOnline = isOnline;
			siteUser.IsConfirmed = isConfirmed;
			siteUser.ConfirmationCode = confirmationCode;
			siteUser.RegisterDate = registerDate;
			siteUser.LastLogin = lastLogin;
			siteUser.SecurityQuestion = securityQuestion;
			siteUser.SecurityAnswer = securityAnswer;
			siteUser.VisitCount = visitCount;
			siteUser.RegisterIp = registerIp;
			siteUser.FailedLoginCount = failedLoginCount;
			siteUser.UserRoleId = userRoleId;

            return (siteUser.Create() ? siteUser : null);
        }

        /// <summary>
        /// Instance creational method. Inserts the current object as a new entry in table.
        /// </summary>
        /// <returns>Boolean result as success or failure</returns>
        public override bool Create()
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddCommandParameter(command, "@UserName", SqlDbType.NVarChar, 50, ParameterDirection.Input, UserName);
			AddCommandParameter(command, "@UserPassword", SqlDbType.NVarChar, 50, ParameterDirection.Input, UserPassword);
			AddCommandParameter(command, "@EmailAddress", SqlDbType.NVarChar, 50, ParameterDirection.Input, EmailAddress);
			AddCommandParameter(command, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, IsActive);
			AddCommandParameter(command, "@IsOnline", SqlDbType.Bit, 0, ParameterDirection.Input, IsOnline);
			AddCommandParameter(command, "@IsConfirmed", SqlDbType.Bit, 0, ParameterDirection.Input, IsConfirmed);
			AddCommandParameter(command, "@ConfirmationCode", SqlDbType.NVarChar, 300, ParameterDirection.Input, ConfirmationCode);
			AddCommandParameter(command, "@RegisterDate", SqlDbType.DateTime, 0, ParameterDirection.Input, RegisterDate);
			AddCommandParameter(command, "@LastLogin", SqlDbType.DateTime, 0, ParameterDirection.Input, LastLogin);
			AddCommandParameter(command, "@SecurityQuestion", SqlDbType.NVarChar, 300, ParameterDirection.Input, SecurityQuestion);
			AddCommandParameter(command, "@SecurityAnswer", SqlDbType.NVarChar, 300, ParameterDirection.Input, SecurityAnswer);
			AddCommandParameter(command, "@VisitCount", SqlDbType.Int, 0, ParameterDirection.Input, VisitCount);
			AddCommandParameter(command, "@RegisterIp", SqlDbType.NVarChar, 50, ParameterDirection.Input, RegisterIp);
			AddCommandParameter(command, "@FailedLoginCount", SqlDbType.Int, 0, ParameterDirection.Input, FailedLoginCount);
			AddCommandParameter(command, "@UserRoleId", SqlDbType.Int, 0, ParameterDirection.Input, UserRoleId);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_SiteUser_Create.ToString());

            Id = (int)GetOneData(command);
            return (Id == 0 ? false : true);
        }

        /// <summary>
        /// Static deletion method. Deletes a record from table using the provided primary key id.
        /// </summary>
        /// <param name="id">Primary key</param>
        /// <returns>Boolean result as success or failure</returns>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Delete, true)]
        public static bool Delete(int id)
        {
            SiteUser siteUser = new SiteUser();
            siteUser.Id = id;

            return siteUser.Delete();
        }

        /// <summary>
        /// Static deletion method. Deletes a list record from table using the provided primary keys id.
        /// </summary>
        /// <param name="ids">List of primarky key id</param>
        /// <returns>Boolean result as success or failure</returns>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Delete, true)]
        public static bool Delete(List<int> ids)
        {
            string csvIds = Utility.MakeCSV(ids);

            SiteUser siteUser = new SiteUser();

            return siteUser.Delete(csvIds);
        }

        /// <summary>
        /// Static deletion method. Deletes records from table using provided conditions.
        /// </summary>
        /// <param name="conditions">Custom condition based on which to match data</param>
        /// <returns>Boolean result as success or failure</returns>
        public static bool Delete(List<Condition> conditions)
        {
            SiteUser siteUser = new SiteUser();

            return siteUser.ConditionalDelete(conditions);
        }

        /// <summary>
        /// This is helper method for conditional delete method above. Not for other general use.
        /// </summary>
        /// <param name="conditions">Custom condition based on which to match data</param>
        /// <returns>Boolean result as success or failure</returns>
        private bool ConditionalDelete(List<Condition> conditions)
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            PrepareConditionalParameters(command, conditions);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_SiteUser_DeleteConditional.ToString());

            int returnValue = (int)GetOneData(command);
            return (returnValue == 0 ? false : true);
        }

        /// <summary>
        /// This is a helper method for multiple id delete method. Not for general use.
        /// </summary>
        /// <param name="csvIds">Comma seperated primary key values as string</param>
        /// <returns>Boolean result as success or failure</returns>
        private bool Delete(string csvIds)
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddCommandParameter(command, "@Ids", SqlDbType.NVarChar, 500, ParameterDirection.Input, csvIds);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_SiteUser_DeleteMultiple.ToString());

            int returnValue = (int)GetOneData(command);
            return (returnValue == 0 ? false : true);
        }

        /// <summary>
        /// Instance deletion method. Deletes the current instance from table.
        /// </summary>
        /// <returns>Boolean result as success or failure</returns>
        public override bool Delete()
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddCommandParameter(command, "@Id", SqlDbType.Int, 0, ParameterDirection.Input, Id);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_SiteUser_DeleteOne.ToString());

            int returnValue = (int)GetOneData(command);
            return (returnValue == 0 ? false : true);
        }

        /// <summary>
        /// Static selection method. Used for loading a perticular data using primary key id.
        /// </summary>
        /// <param name="id">Primary key</param>
        /// <returns>Instance of the loaded object</returns>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static SiteUser Load(int id)
        {
            SiteUser siteUser = new SiteUser();
            siteUser.Id = id;

            if (siteUser.Load())
                return siteUser;
            else
                return null;
        }

        /// <summary>
        /// Instance selection method that fetches data using paging and sorting and also with 
        /// custom conditions.
        /// </summary>
        /// <param name="oderBy">Sorting order</param>
        /// <param name="startRow">Page start position</param>
        /// <param name="pageSize">Number of maximum record to be shown</param>
        /// <param name="conditions">Custom condition based on which to match data</param>
        /// <returns>List of instances of loaded objects</returns>
        public List<SiteUser> Load(string orderBy, int startRow, int pageSize, List<Condition> conditions)
        {
            List<SiteUser> siteUsers = new List<SiteUser>();
			
			SqlCommand command = (SqlCommand)CreateCommand();
			
            PrepareConditionalParameters(command, conditions);

            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddCommandParameter(command, "@OrderBy", SqlDbType.NVarChar, 50, ParameterDirection.Input, orderBy);
            AddCommandParameter(command, "@StartRow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddCommandParameter(command, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_SiteUser_LoadConditionalPaged.ToString());

            List<BaseDAO> dataList = GetListData(command);

            foreach (BaseDAO data in dataList)
            {
                SiteUser siteUser = (SiteUser)data;
                siteUsers.Add(siteUser);
            }

            return siteUsers;
        }

        /// <summary>
        /// Counts the number of total record available
        /// </summary>
        /// <param name="conditions">Custom condition based on which to match data</param>
        /// <returns>Count of the records</returns>
        public int LoadCount(List<Condition> conditions)
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            PrepareConditionalParameters(command, conditions);

            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_SiteUser_LoadConditionalPagedCount.ToString());

            int returnValue = (int)GetOneData(command);
            return returnValue;
        }

        /// <summary>
        /// Instance selection method that fetches data using paging and sorting.
        /// </summary>
        /// <param name="oderBy">Sorting order</param>
        /// <param name="startRow">Page start position</param>
        /// <param name="pageSize">Number of maximum record to be shown</param>
        /// <returns>List of instances of loaded objects</returns>
        public List<SiteUser> Load(string orderBy, int startRow, int pageSize)
        {
            List<SiteUser> siteUsers = new List<SiteUser>();
            
			SqlCommand command = (SqlCommand)CreateCommand();
			
            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddCommandParameter(command, "@OrderBy", SqlDbType.NVarChar, 50, ParameterDirection.Input, orderBy);
            AddCommandParameter(command, "@StartRow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddCommandParameter(command, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_SiteUser_LoadPaged.ToString());

            List<BaseDAO> dataList = GetListData(command);

            foreach (BaseDAO data in dataList)
            {
                SiteUser siteUser = (SiteUser)data;
                siteUsers.Add(siteUser);
            }

            return siteUsers;
        }

        /// <summary>
        /// Counts the number of total record available
        /// </summary>
        /// <returns>Count of the records</returns>
        public int LoadCount()
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_SiteUser_LoadPagedCount.ToString());

            int returnValue = (int)GetOneData(command);
            return returnValue;
        }

        /// <summary>
        /// Instance selection method. Used for reloading the current object from table. This method uses
        /// the Id of the current object to load and expects that the Id is correct.
        /// </summary>
        /// <returns>Boolean result as success or failure</returns>
        public override bool Load()
        {
			SqlCommand command = (SqlCommand)CreateCommand();
			
            AddCommandParameter(command, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddCommandParameter(command, "@Id", SqlDbType.Int, 0, ParameterDirection.Input, Id);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_SiteUser_Load.ToString());

            List<BaseDAO> dataList = GetListData(command);

            foreach (BaseDAO data in dataList)
            {
                SiteUser siteUser = (SiteUser)data;

                this._id = siteUser._id;
				this._userName = siteUser._userName;
				this._userPassword = siteUser._userPassword;
				this._emailAddress = siteUser._emailAddress;
				this._isActive = siteUser._isActive;
				this._isOnline = siteUser._isOnline;
				this._isConfirmed = siteUser._isConfirmed;
				this._confirmationCode = siteUser._confirmationCode;
				this._registerDate = siteUser._registerDate;
				this._lastLogin = siteUser._lastLogin;
				this._securityQuestion = siteUser._securityQuestion;
				this._securityAnswer = siteUser._securityAnswer;
				this._visitCount = siteUser._visitCount;
				this._registerIp = siteUser._registerIp;
				this._failedLoginCount = siteUser._failedLoginCount;
				this._userRoleId = siteUser._userRoleId;

                break;
            }

            return (dataList.Count > 0 ? true : false);
        }

        /// <summary>
        /// Instance selection method. Used for loading multiple record based on provided conditions.
        /// </summary>
        /// <param name="conditions">Custom condition based on which to match data</param>
        /// <returns>List of instances of loaded objects</returns>
        public List<SiteUser> Load(List<Condition> conditions)
        {
            List<SiteUser> siteUsers = new List<SiteUser>();

			SqlCommand command = (SqlCommand)CreateCommand();
			
            PrepareConditionalParameters(command, conditions);

            SetCommandType(command, CommandType.StoredProcedure, StoreProcedureName.Sp_SiteUser_LoadConditional.ToString());

            List<BaseDAO> dataList = GetListData(command);

            foreach (BaseDAO data in dataList)
            {
                SiteUser siteUser = (SiteUser)data;
                siteUsers.Add(siteUser);
            }

            return siteUsers;
        }

        /// <summary>
        /// Prepares the conditional parameters for use with Create/ Save/ Load/ Delete methods
        /// that use custom condition. Prepares the user Command object for using conditions.
        /// </summary>
        /// <param name="conditions">Custom condition based on which to match data</param>
        private void PrepareConditionalParameters(SqlCommand command, List<Condition> conditions)
        {
            StringBuilder condition = new StringBuilder();

            object id = null;
			object userName = null;
			object userPassword = null;
			object emailAddress = null;
			object isActive = null;
			object isOnline = null;
			object isConfirmed = null;
			object confirmationCode = null;
			object registerDate = null;
			object lastLogin = null;
			object securityQuestion = null;
			object securityAnswer = null;
			object visitCount = null;
			object registerIp = null;
			object failedLoginCount = null;
			object userRoleId = null;

            for (int i = 0; i < conditions.Count; i++)
            {
                string fieldName = Utility.ToHungarianNotation(conditions[i].FieldName);

                if (conditions[i].JoiningCondition == JoinOperator.FirstValue)
                {
                    condition.Append("WHERE ");
                }
                else if (conditions[i].JoiningCondition == JoinOperator.And)
                {
                    condition.Append(" AND ");
                }
                else if (conditions[i].JoiningCondition == JoinOperator.Or)
                {
                    condition.Append(" OR ");
                }

                condition.Append(conditions[i].FieldName).Append(" ").Append(Condition.MapCondition(conditions[i].ConditionalOperator)).Append("@" + fieldName);

                if ((conditions[i].ConditionalOperator == ConditionOperator.Like ||
                    conditions[i].ConditionalOperator == ConditionOperator.NotLike))
                {
                    if (string.IsNullOrEmpty(conditions[i].Value.ToString()))
                        conditions[i].Value = "%%%";
                    else
                        conditions[i].Value = "%" + conditions[i].Value.ToString() + "%";
                }

                if (fieldName == TableColumns.Id.ToString())
                    id = conditions[i].Value;
                else if (fieldName == TableColumns.UserName.ToString())
					userName = conditions[i].Value;
				else if (fieldName == TableColumns.UserPassword.ToString())
					userPassword = conditions[i].Value;
				else if (fieldName == TableColumns.EmailAddress.ToString())
					emailAddress = conditions[i].Value;
				else if (fieldName == TableColumns.IsActive.ToString())
					isActive = conditions[i].Value;
				else if (fieldName == TableColumns.IsOnline.ToString())
					isOnline = conditions[i].Value;
				else if (fieldName == TableColumns.IsConfirmed.ToString())
					isConfirmed = conditions[i].Value;
				else if (fieldName == TableColumns.ConfirmationCode.ToString())
					confirmationCode = conditions[i].Value;
				else if (fieldName == TableColumns.RegisterDate.ToString())
					registerDate = conditions[i].Value;
				else if (fieldName == TableColumns.LastLogin.ToString())
					lastLogin = conditions[i].Value;
				else if (fieldName == TableColumns.SecurityQuestion.ToString())
					securityQuestion = conditions[i].Value;
				else if (fieldName == TableColumns.SecurityAnswer.ToString())
					securityAnswer = conditions[i].Value;
				else if (fieldName == TableColumns.VisitCount.ToString())
					visitCount = conditions[i].Value;
				else if (fieldName == TableColumns.RegisterIp.ToString())
					registerIp = conditions[i].Value;
				else if (fieldName == TableColumns.FailedLoginCount.ToString())
					failedLoginCount = conditions[i].Value;
				else if (fieldName == TableColumns.UserRoleId.ToString())
					userRoleId = conditions[i].Value;
            }

            AddCommandParameter(command, "@Condition", SqlDbType.NVarChar, 2000, ParameterDirection.Input, condition.ToString());
            AddCommandParameter(command, "@Id", SqlDbType.Int, 0, ParameterDirection.Input, id);
			AddCommandParameter(command, "@UserName", SqlDbType.NVarChar, 50, ParameterDirection.Input, userName);
			AddCommandParameter(command, "@UserPassword", SqlDbType.NVarChar, 50, ParameterDirection.Input, userPassword);
			AddCommandParameter(command, "@EmailAddress", SqlDbType.NVarChar, 50, ParameterDirection.Input, emailAddress);
			AddCommandParameter(command, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, isActive);
			AddCommandParameter(command, "@IsOnline", SqlDbType.Bit, 0, ParameterDirection.Input, isOnline);
			AddCommandParameter(command, "@IsConfirmed", SqlDbType.Bit, 0, ParameterDirection.Input, isConfirmed);
			AddCommandParameter(command, "@ConfirmationCode", SqlDbType.NVarChar, 300, ParameterDirection.Input, confirmationCode);
			AddCommandParameter(command, "@RegisterDate", SqlDbType.DateTime, 0, ParameterDirection.Input, registerDate);
			AddCommandParameter(command, "@LastLogin", SqlDbType.DateTime, 0, ParameterDirection.Input, lastLogin);
			AddCommandParameter(command, "@SecurityQuestion", SqlDbType.NVarChar, 300, ParameterDirection.Input, securityQuestion);
			AddCommandParameter(command, "@SecurityAnswer", SqlDbType.NVarChar, 300, ParameterDirection.Input, securityAnswer);
			AddCommandParameter(command, "@VisitCount", SqlDbType.Int, 0, ParameterDirection.Input, visitCount);
			AddCommandParameter(command, "@RegisterIp", SqlDbType.NVarChar, 50, ParameterDirection.Input, registerIp);
			AddCommandParameter(command, "@FailedLoginCount", SqlDbType.Int, 0, ParameterDirection.Input, failedLoginCount);
			AddCommandParameter(command, "@UserRoleId", SqlDbType.Int, 0, ParameterDirection.Input, userRoleId);
        }

        /// <summary>
        /// Binds the fetched data from data reader to Objects
        /// </summary>
        /// <param name="reader">Data reader supplied by BaseDAO</param>
        /// <returns>List of object instances that was bound</returns>
        protected override List<BaseDAO> BindData(DbDataReader reader)
        {
            List<BaseDAO> result = new List<BaseDAO>();

			if (reader == null)
                return null;

            while (reader.Read())
            {
                SiteUser siteUser = new SiteUser
                (
					reader["id"] == System.DBNull.Value ? GetIdMinValue : (int)reader["id"],
                    reader["user_name"] == System.DBNull.Value ? string.Empty : (string)reader["user_name"],
					reader["user_password"] == System.DBNull.Value ? string.Empty : (string)reader["user_password"],
					reader["email_address"] == System.DBNull.Value ? string.Empty : (string)reader["email_address"],
					reader["is_active"] == System.DBNull.Value ? false : (bool)reader["is_active"],
					reader["is_online"] == System.DBNull.Value ? false : (bool)reader["is_online"],
					reader["is_confirmed"] == System.DBNull.Value ? false : (bool)reader["is_confirmed"],
					reader["confirmation_code"] == System.DBNull.Value ? string.Empty : (string)reader["confirmation_code"],
					reader["register_date"] == System.DBNull.Value ? SqlDateTime.MinValue.Value : (DateTime)reader["register_date"],
					reader["last_login"] == System.DBNull.Value ? SqlDateTime.MinValue.Value : (DateTime)reader["last_login"],
					reader["security_question"] == System.DBNull.Value ? string.Empty : (string)reader["security_question"],
					reader["security_answer"] == System.DBNull.Value ? string.Empty : (string)reader["security_answer"],
					reader["visit_count"] == System.DBNull.Value ? 0 : (int)reader["visit_count"],
					reader["register_ip"] == System.DBNull.Value ? string.Empty : (string)reader["register_ip"],
					reader["failed_login_count"] == System.DBNull.Value ? 0 : (int)reader["failed_login_count"],
					reader["user_role_id"] == System.DBNull.Value ? 0 : (int)reader["user_role_id"]
                );

                result.Add(siteUser);
            }
            
            if(!reader.IsClosed)
                reader.Close();

            return result;
        }

        /// <summary>
        /// List of store procedures that this class use
        /// </summary>
        private enum StoreProcedureName
        {
            Sp_SiteUser_Save = 0,
            Sp_SiteUser_Create = 1,
            Sp_SiteUser_DeleteOne = 2,
            Sp_SiteUser_DeleteMultiple = 3,
            Sp_SiteUser_DeleteConditional = 4,
            Sp_SiteUser_SaveConditional = 5,
            Sp_SiteUser_LoadConditional = 6,
            Sp_SiteUser_Load = 7,
            Sp_SiteUser_LoadConditionalPaged = 8,
            Sp_SiteUser_LoadConditionalPagedCount = 9,
            Sp_SiteUser_LoadPaged = 10,
            Sp_SiteUser_LoadPagedCount = 11
        }

        /// <summary>
        /// List of table column names that this class use
        /// </summary>
        private enum TableColumns
        {
            Id = 0,
			UserName = 1,
			UserPassword = 2,
			EmailAddress = 3,
			IsActive = 4,
			IsOnline = 5,
			IsConfirmed = 6,
			ConfirmationCode = 7,
			RegisterDate = 8,
			LastLogin = 9,
			SecurityQuestion = 10,
			SecurityAnswer = 11,
			VisitCount = 12,
			RegisterIp = 13,
			FailedLoginCount = 14,
			UserRoleId = 15
        }
    }
}